package com.dong.dao.impl;

import com.dong.dao.BaseDao;
import com.dong.dao.StudentDao;
import com.dong.entity.Student;
import com.dong.util.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xiaoDong
 * @version 1.0.0
 * @ClassName StudentDaoImpl.java
 * @Description TODO
 * @createTime 2024年05月07日 08:58:00
 */
public class StudentDaoImpl extends BaseDao implements StudentDao {
    @Override
    public int count(String stuName, Integer classId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT count(*) count");
        sb.append(" FROM student s, class c ");
        sb.append(" WHERE ");
        sb.append(" s.classId = c.id ");
        List<Object> parms = new ArrayList<>();
        if (!"".equals(stuName) && stuName != null) {
            sb.append(" AND s.userName LIKE CONCAT ('%',?,'%')");
            parms.add(stuName);
        }
        if (classId != null && classId != -1) {
            sb.append(" AND c.id = ?");
            parms.add(classId);
        }
        ResultSet resultSet = executeQuery(sb.toString(), parms.toArray());
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closedAll(resultSet);
        }

        return 0;
    }

    @Override
    public List<Student> selectList(Page page, String stuName, Integer classId) {

        List<Student> studentList = new ArrayList<>();

        StringBuilder sb = new StringBuilder();
        sb.append(" SELECT s.*, c.className,  ");
        sb.append(" FLOOR(DATEDIFF(NOW(),s.birthday)/365) age");
        sb.append(" FROM student s,class c ");
        sb.append(" WHERE ");
        sb.append(" s.classId = c.id ");
        List<Object> parms = new ArrayList<>();
        if (!"".equals(stuName) && stuName != null) {
            sb.append(" AND s.userName LIKE CONCAT ('%',?,'%')");
            parms.add(stuName);
        }
        if (classId != null && classId != -1) {
            sb.append(" AND c.id = ?");
            parms.add(classId);
        }
        sb.append(" LIMIT ?,?");
        parms.add((page.getPageNum() - 1)*page.getCount());
        parms.add(page.getCount());


        ResultSet resultSet = executeQuery(sb.toString(), parms.toArray());

        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setUserName(resultSet.getString("userName"));
                    student.setImgPath(resultSet.getString("imgPath"));
                    student.setClassId(resultSet.getInt("classId"));
                    student.setClassName(resultSet.getString("className"));
                    student.setSex(resultSet.getString("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setAge(resultSet.getInt("age"));
                    student.setAddress(resultSet.getString("address"));
                    student.setState(resultSet.getString("state"));
                    studentList.add(student);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closedAll(resultSet);
            }
        }

        return studentList;
    }


    @Override
    public int addStu(Student student) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(" INSERT INTO student (");
        stringBuilder.append(" student.userName,student.sex,student.phone,");
        stringBuilder.append(" student.imgPath,student.classId,student.birthday,student.address)");
        stringBuilder.append(" VALUES(?,?,?,?,?,?,?)");

        return executeUpdate(stringBuilder.toString(),
                student.getUserName(), student.getSex(), student.getPhone(),
                student.getImgPath(), student.getClassId(), student.getBirthday(), student.getAddress());


    }

    @Override
    public Student stuDetail(int id) {
        String sql = "SELECT * FROM student s WHERE s.id=?";
        ResultSet resultSet = executeQuery(sql, id);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    Student student = new Student();
                    student.setId(resultSet.getInt("id"));
                    student.setUserName(resultSet.getString("userName"));
                    student.setImgPath(resultSet.getString("imgPath"));
                    student.setClassId(resultSet.getInt("classId"));
                    student.setSex(resultSet.getString("sex"));
                    student.setPhone(resultSet.getString("phone"));
                    student.setBirthday(resultSet.getString("birthday"));
                    student.setAddress(resultSet.getString("address"));
                    student.setState(resultSet.getString("state"));
                    return student;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closedAll(resultSet);
            }
        }


        return null;
    }


    @Override
    public int updateStu(Student student) {
        StringBuilder sb = new StringBuilder();

        sb.append(" UPDATE student SET  ");
        sb.append(" userName = ?,imgPath=?,classId=?, ");
        sb.append(" sex=?,phone=?,birthday=?,address=?");
        sb.append(" WHERE id = ?");

        return executeUpdate(sb.toString(), student.getUserName(),
                student.getImgPath(), student.getClassId(),
                student.getSex(), student.getPhone(), student.getBirthday(),
                student.getAddress(), student.getId());

    }
}
